IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserAccessRightsToClinicalTrials]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserAccessRightsToClinicalTrials]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.GetUserAccessRightsToClinicalTrials (
		@domainUserId			INTEGER,
		@budgetId				INTEGER
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @pendingBudgetReadAccessBit INT
DECLARE @finalBudgetReadAccessBit INT
DECLARE @pendingBudgetModifyAccessBit INT

SET @pendingBudgetReadAccessBit = 8192
SET @finalBudgetReadAccessBit = 4096
SET @pendingBudgetModifyAccessBit = 32768

-- get list of budgets rights
		SELECT DISTINCT 
			p.FwkResourceId  as ResourceId
			,Cast(p.Id as VARCHAR) as RecordNumber
			,p.StudyShortTitle as Description
			,ap.LastName + ', ' +  ap.FirstName as Investigator
			,rdum.FwkDomainUserId
			,rdum.AccessRightBitmask			
			,CASE 
				WHEN ((AccessRightBitmask & @pendingBudgetReadAccessBit) > 0) THEN 1 
				ELSE 0
			END as IsReadPending
			,CASE 
				WHEN ((AccessRightBitmask & @finalBudgetReadAccessBit) > 0) THEN 1 
				ELSE 0
			END as IsReadFinal
			,CASE 
				WHEN ((AccessRightBitmask & @pendingBudgetModifyAccessBit) > 0) THEN 1 
				ELSE 0
			END AS IsModifyPending	
			,fdu.UserName
			,p.OriginatorPersonId
			,p.PIPersonId					
		FROM ClinicalTrialBudget p
		INNER JOIN admPerson ap 
		ON p.OriginatorPersonId = ap.Id
		INNER JOIN FwkResource_DomainUser_Map rdum
		ON p.FwkResourceId = rdum.FwkResourceId	
		INNER JOIN 	FwkDomainUser fdu
		ON fdu.Id = rdum.FwkDomainUserId
		CROSS JOIN AccessRightType art
		INNER JOIN ModuleType mt
		ON art.ModuleBitmask & mt.bitvalue = mt.bitvalue
		INNER JOIN AdmInternalPerson aip
			ON aip.AdmPersonId = ap.Id
		WHERE rdum.FwkDomainUserId = @domainUserId
			and p.Id = @budgetId
END


GO
